
import re, string, glob, sys
if sys.platform == 'win32' or sys.version[:3] in ('2.4'):
    from pysqlite2 import dbapi2 as sqlite3 #if windoze
else:
    import sqlite3 #if other OS, or if python 2.5+

def main(f1, f2):
    """ join (and report differences of) two tables
    """
    con = sqlite3.connect(':memory:')
    #con = sqlite3.connect('sdb')
    cur = con.cursor()
    cur.execute('CREATE TABLE t (f VARCHAR(100), k VARCHAR(100), v VARCHAR(1000))')
    con.commit()
    # read rows from files
    for fn in [f1, f2]:
        o = open(fn, 'r')
        rows = o.readlines()
        o.close()
        # prepare and insert rows into db
        for r in rows:
            r = re.sub('\r\n', '', r)
            r = re.sub('\n', '', r)
            rk, rv = string.split(r, '\t', 1)
            # to get ... 1110200	<k:v>	.NULL	0
            #rk = rk + '\t<k:v>\t'
            rk = string.strip(rk)
            cur.execute('INSERT INTO t (f, k, v) VALUES("%s", "%s", "%s")' % (fn, rk, rv))
            con.commit()
    
    s = """
      SELECT t0.k, t1.v, t2.v
             from (select distinct k from t order by k) t0
             left join (select * from t where f = '%s') t1 on t0.k = t1.k 
             left join (select * from t where f = '%s') t2 on t0.k = t2.k
    """ % (f1, f2)
    cur.execute(s)
    o = open('join_dat_sqlite.jdat#', 'w')
    o.write('# JOINED DATA PER FIRST (KEY) COLUMN\n')
    o.write('#  NOTE: VALUE CAN BE DIFFERENT, WE DO NOT CARE HERE\n')
    o.write('#  TAB EXPECTED AS DELIMITER IN SOURCE FILES\n\n_\n')
    for i in cur.fetchall():
        r = []
        # care about None cases (missing values)
        for y in i:
            if y is None: r.append('.NULL')
            else: r.append(y)
        #print string.join(i, '\t')
        o.write(unicode(string.join(r, '\t')).encode("utf-8") + '\n')
    o.close()
    s = """
     SELECT f, k, v
       FROM t
      GROUP BY k, v
     HAVING COUNT(*) = 1
      ORDER BY k
     """
    cur.execute(s)
    o = open('join_dat_sqlite.diffs.jdat#', 'w')
    o.write('# UNIQUE COMBINATION OF KEY-VALUE PAIR, POSSIBLY:\n')
    o.write('#  LONELY KEY      - KEY (AND VALUE) IN ONE FILE ONLY\n')
    o.write('#  DIFFERENT VALUE - FOR GIVEN KEY DIFFERENT VALUES NOTED\n\n_\n')
    for i in cur.fetchall():
        r = []
        for y in i:
            if y is None: r.append('.NULL')
            else: r.append(y)
        #print string.join(i, '\t')
        o.write(unicode(string.join(r, '\t')).encode("utf-8") + '\n')
    o.close()

if __name__ == "__main__":
    ''' '''
    if  len(sys.argv) <> 3:
        sys.stderr.write('Usage: join-dat-sqlite FILE1 FILE2\n')
        sys.exit(2)
    else:
        main(sys.argv[1], sys.argv[2])